#!/usr/bin/env bash

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
truncate table realtime.TBL_ORDERDETAILS_ALL;
insert into  realtime.TBL_ORDERDETAILS_ALL
select ORDERDETAILS_ID    ,
VERSION            ,
NOWDATE            ,
VIRTUALGOODSID     ,
RETURNCODE         ,
LITRE              ,
if(STATIONCODE = '58008','58010',STATIONCODE)       ,
COUPONAMOUNT       ,
REQUESTID          ,
ORDERNUM           ,
REALAMOUNT         ,
OILCODE            ,
ORDER_DEAL         ,
DISCOUNTAMOUNT     ,
CARDUSERID         ,
AMOUNT             ,
OILPRICE           ,
TERMNUM            ,
SENDTIME           ,
INVOICETITLE       ,
MEMBERNO           ,
VIRTUALGOODSIDS    ,
CUSTOMER_ID        ,
create_shijian     ,
PPSORDERNUMBER     ,
GOODSAMOUNT        ,
FUELCOUPONAMOUNT   ,
GOODCOUPONAMOUNT   ,
GOODSCOUPONAMOUNT  ,
UPDATEREALAMOUNT   ,
FULECOUPONAMOUNT   ,
FULEAMOUNT         ,
CARLICENSE         ,
OILGUNNO           ,
ORDER_STATE        ,
EPSCODE  from oracleanditem.TBL_ORDERDETAILS
where SENDTIME>='$dt'
union all
select  ORDERDETAILS_ID    ,
VERSION            ,
NOWDATE            ,
VIRTUALGOODSID     ,
RETURNCODE         ,
LITRE              ,
if(STATIONCODE = '58008','58010',STATIONCODE)       ,
COUPONAMOUNT       ,
REQUESTID          ,
ORDERNUM           ,
REALAMOUNT         ,
OILCODE            ,
ORDER_DEAL         ,
DISCOUNTAMOUNT     ,
CARDUSERID         ,
AMOUNT             ,
OILPRICE           ,
TERMNUM            ,
SENDTIME           ,
INVOICETITLE       ,
MEMBERNO           ,
VIRTUALGOODSIDS    ,
CUSTOMER_ID        ,
create_shijian     ,
PPSORDERNUMBER     ,
GOODSAMOUNT        ,
FUELCOUPONAMOUNT   ,
GOODCOUPONAMOUNT   ,
GOODSCOUPONAMOUNT  ,
UPDATEREALAMOUNT   ,
FULECOUPONAMOUNT   ,
FULEAMOUNT         ,
CARLICENSE         ,
OILGUNNO           ,
ORDER_STATE        ,
EPSCODE from oraclecd.TBL_ORDERDETAILS
where SENDTIME>='$dt';

TRUNCATE TABLE realtime.transactions_vip;
insert INTO realtime.transactions_vip
select B.CARDUSERID,A.*
from
(select * from transactions where transaction_date>='$dt' )
 as A
join realtime.TBL_ORDERDETAILS_ALL as B
on A.transaction_number = B.ORDERNUM and A.station_id = B.STATIONCODE and A.sale_total = B.AMOUNT/100 AND ORDER_DEAL = 4 and substr(A.transaction_date,1,10) = substr(B.SENDTIME,1,10);

TRUNCATE TABLE realtime.transaction_items_mop_vip;
INSERT into realtime.transaction_items_mop_vip
select A.carduser_id,B.*
from realtime.transactions_vip as A
JOIN transaction_items_mop as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id;

TRUNCATE TABLE realtime.transaction_items_notoil_vip;
INSERT into realtime.transaction_items_notoil_vip
select A.carduser_id,B.*
from realtime.transactions_vip as A
JOIN transaction_items_notoil as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id;

TRUNCATE TABLE realtime.transaction_items_oil_vip;
INSERT into realtime.transaction_items_oil_vip
select A.carduser_id,B.*
from realtime.transactions_vip as A
JOIN transaction_items_oil as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id;
EOF